package com.ccjq.shosestore.product.dao.impl;

import com.ccjq.shosestore.core.dbmanager.DbConnection;
import com.ccjq.shosestore.product.bo.Category;
import com.ccjq.shosestore.product.bo.Product;
import com.ccjq.shosestore.product.bo.Slider;
import com.ccjq.shosestore.product.dao.ProductDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductImpl implements ProductDao {
    Connection conn = null;
    PreparedStatement st = null;
    ResultSet re = null;

    /**
     * 查询品牌表中内容
     *
     * @return 返回一个list集合
     */
    public List<Category> pinpai() {
        List<Category> list = new ArrayList<Category>();
        Category cat = null;
        String sql = "select * from  t_category";
        conn = DbConnection.getConnextion();
        try {
            st = conn.prepareStatement(sql);
            re = st.executeQuery();
            while (re.next()) {
                cat = new Category();
                cat.setCategory_id(re.getString(1));
                cat.setName(re.getString(2));
                list.add(cat);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(re, st, conn);
        }
        return list;
    }

    public Product details(String productId) {
        String xiangqing="select * from t_product where product_id=?";
        Product product=new Product();
        try {
            conn=DbConnection.getConnextion();
            st=conn.prepareStatement(xiangqing);
            st.setString(1,productId);
            re=st.executeQuery();
            if(re.next()){
                product.setProduct_id(re.getString("product_id"));
                product.setDescription(re.getString("description"));
                product.setPrice(re.getDouble("price"));
                product.setModel(re.getString("model"));
                product.setFeature(re.getString("feature"));
                product.setImgpath(re.getString("imgpath"));
                product.setNovalty_status(re.getInt("novalty_status"));
                product.setPublish_date(re.getDate("publish_date"));
                product.setCategory_id(re.getString("category_id"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return product;
    }

    /**
     * 通过品牌ID，查询指定显示商品，并做分页
     *
     * @param catid
     * @return
     */
    public List<Product> pinpaiAll(int startRow, int pageSize, String catid) {
        List<Product> list = new ArrayList<Product>();
        String sql=null;
        if(catid.length()!=0)
        {
             sql = "select top  " + pageSize + " * from  t_product where product_id not in(select top " + --startRow + " product_id from  t_product where category_id='"+catid+"')and category_id='"+catid+"' ";
        }
        else
        {
             sql = "select top " + pageSize + " * FROM T_PRODUCT WHERE PRODUCT_ID NOT in (select top " + --startRow + " PRODUCT_ID from T_PRODUCT )" ;
        }
        Product pr = null;
        conn = DbConnection.getConnextion();
        try {
            st = conn.prepareStatement(sql);
            re = st.executeQuery();
            while (re.next()) {
                pr = new Product();
                pr.setProduct_id(re.getString(1));
                pr.setDescription(re.getString(2));
                pr.setPrice(re.getDouble(3));
                pr.setModel(re.getString(4));
                pr.setFeature(re.getString(5));
                pr.setImgpath(re.getString(6));
                pr.setNovalty_status(re.getInt(7));
                pr.setPublish_date(re.getDate(8));
                pr.setCategory_id(re.getString(9));
                list.add(pr);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(re, st, conn);
        }
        return list;
    }

    /**
     * 根据品牌ID 查询总页数
     *
     * @param catid
     * @return
     */
    public int zhs(String catid) {
        int a = 0;
        String sql=null;
        if(catid.length()!=0)
        {
            sql = "select count(*) from  t_product where category_id='"+catid+"'";
        }
        else
        {
            sql = "select count(*) from  t_product";
        }
        conn = DbConnection.getConnextion();
        try {
            st = conn.prepareStatement(sql);
            re = st.executeQuery();
            if (re.next()) {
                a = re.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(re, st, conn);
        }
        return a;
    }

    /**
     * 最新商品
     *
     * @return
     */

    public List<Product> xinpin() {
        List<Product> list = new ArrayList<Product>();
        Product pr = null;
        String sql = "select top 6 * from t_product where novalty_status=1";
        conn = DbConnection.getConnextion();
        try {
            st = conn.prepareStatement(sql);
            re = st.executeQuery();
            while (re.next()) {
                pr = new Product();
                pr.setProduct_id(re.getString(1));
                pr.setDescription(re.getString(2));
                pr.setPrice(re.getDouble(3));
                pr.setModel(re.getString(4));
                pr.setFeature(re.getString(5));
                pr.setImgpath(re.getString(6));
                pr.setNovalty_status(re.getInt(7));
                pr.setPublish_date(re.getDate(8));
                pr.setCategory_id(re.getString(9));
                list.add(pr);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(re, st, conn);
        }
        return list;
    }

    /**
     * 热卖
     *
     * @return
     */
    public List<Product> remai() {
        List<Product> list = new ArrayList<Product>();
        Product pr = null;
        String sql = "select top 3 * from t_product where novalty_status=1";
        conn = DbConnection.getConnextion();
        try {
            st = conn.prepareStatement(sql);
            re = st.executeQuery();
            while (re.next()) {
                pr = new Product();
                pr.setProduct_id(re.getString(1));
                pr.setDescription(re.getString(2));
                pr.setPrice(re.getDouble(3));
                pr.setModel(re.getString(4));
                pr.setFeature(re.getString(5));
                pr.setImgpath(re.getString(6));
                pr.setNovalty_status(re.getInt(7));
                pr.setPublish_date(re.getDate(8));
                pr.setCategory_id(re.getString(9));
                list.add(pr);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(re, st, conn);
        }
        return list;
    }

    public List<Product> chanpinAll(int startRow, int pageSize) {
        return null;
    }

    public int count() {
        return 0;
    }
    public List<Slider> slider() {
        List<Slider> list=new ArrayList<Slider>();
        try {
            String sql="select * from T_SLIDER ";
            conn=DbConnection.getConnextion();
            st=conn.prepareStatement(sql);
            re=st.executeQuery();
            while(re.next()){
                Slider slider=new Slider();
                slider.setProduct_id(re.getString("product_id"));
                slider.setDescription(re.getString("description"));
                slider.setImgpath(re.getString("imgpath"));
                slider.setAvailability(re.getString("availability"));
                list.add(slider);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int Selectprice(String prid) {
        return 0;
    }

}
